package com.rankeiot.core.sqltoy;

import com.rankeiot.core.Current;
import com.rankeiot.core.data.Pair;
import com.rankeiot.core.util.StringUtil;


import java.util.*;
import java.util.function.Consumer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * SQL 构建工具 NamedQuery
 * eg:Query.select(*).from(Domain).eq(Domain::getPropOne).optional().in(Domain::getPropTow)
 * 生成的sql: SELECT * FROM table_domain WHERE prop_one=:propOne #[AND prop_to=:propTow]
 */
public class Sql {
    public static Sql select(String columns) {
        return new Sql(columns);
    }

    public static <T,R> Sql select(Getter<T,R>... columns) {
        return new Sql(columns);
    }
    public static <T,R> Sql select(String ... columns) {
       List<String> realColumns= Arrays.stream(columns).map(s->toUnderline(s)).collect(Collectors.toList());
        return new Sql(realColumns);
    }
    private List<String> selects = new ArrayList<>();
    private StringBuffer wherePart=new StringBuffer();
    private String table;
    private boolean optional=false;

    private StringBuilder orderBy=new StringBuilder();
    private static final Pattern pattern = Pattern.compile("\\s+(AND|OR)\\s*$");

    private Sql(String columns) {
        if (StringUtil.isNotEmpty(columns)) {
            selects.addAll(Arrays.asList(columns.split(",")));
        } else {
            selects.add("*");
        }
    }
    private Sql(Collection<String> columns){
        selects.addAll(columns);
    }
    private Sql(Getter... columns) {
        for (Getter column : columns) {
            Pair<String,String> columnAndField=getColumnAndField(column);
            selects.add(columnAndField.getValue()+" "+columnAndField.getKey());
        }
    }
    public Sql self(){
        return this;
    }
//    public <T,R> Sql select(Getter<T, R> column){
//        Pair<String,String> columnAndField=getColumnAndField(column);
//        selects.add(columnAndField.getValue()+" "+columnAndField.getKey());
//        return this;
//    }
    public Sql from(String table) {
        this.table = table;
        return this;
    }

    public <T> Sql from(T table) {
        return from(table.getClass());
    }

    public <T> Sql from(Class tableEntity) {
        table=ColumnInfo.getTableName(tableEntity);
        return this;
    }
    public Sql where(){
        return this;
    }

    /**
     * 可选的查询条件,输入值为空是，自动去除该查询条件"
     * @param c
     * @return
     */
    public Sql optional(Consumer<Sql> c){
        this.filter();
        c.accept(this);
        this.unFilter();
        return this;
    }
    public Sql where(String sql){
        return append(sql);
    }
    public Sql append(String sqlPart) {
        wherePart.append(sqlPart);
        return this;
    }

    public Sql and() {
        wherePart.append(" AND ");
        return this;
    }

    public Sql or() {
        wherePart.append(" OR ");
        return this;
    }
    public Sql not() {
        wherePart.append(" NOT ");
        return this;
    }
    /**
     * 转换为过滤查询,即 #[xx=:xx]
     * @return
     */
    public Sql filter(){
        this.optional=true;
        return this;
    }

    /**
     * 转换为非过滤查询，即：xx=:xx
     * @return
     */
    public Sql unFilter(){
        this.optional=false;
        return this;
    }
    public <T, R> Sql eq(Getter<T, R> column) {
        return operate("=", column);
    }

    public <T, R> Sql in(Getter<T, R> column) {
        return operate("in", column);
    }

    public <T, R> Sql lt(Getter<T, R> column) {
        return operate("<", column);
    }

    public <T, R> Sql gt(Getter<T, R> column) {
        return operate(">", column);
    }

    public <T, R> Sql ge(Getter<T, R> column) {
        return operate(">=", column);
    }

    public <T, R> Sql le(Getter<T, R> column) {
        return operate("<=", column);
    }

    public <T, R> Sql ne(Getter<T, R> column) {
        return operate("<>", column);
    }

    public <T, R> Sql like(Getter<T, R> column) {
        return operate("like", column);
    }

    public Sql eq(String field){
        return eq(toUnderline(field),field);
    }
    //等于
    public Sql eq(Enum field){
        ColumnInfo column=EnumColumnUtil.getInfo(field);
        return eq(column.getColumn(),column.getField());
    }
    public Sql eq(String column, String filed){
        return operate("=",column,filed);
    }
    public Sql in(String field){
        return in(toUnderline(field),field);
    }
    public Sql in(Enum field){
        ColumnInfo column=EnumColumnUtil.getInfo(field);
        return in(column.getColumn(),column.getField());
    }
    public Sql in(String column, String filed){
        return operate("in",column,filed);
    }

    public Sql lt(Enum field){
        ColumnInfo column=EnumColumnUtil.getInfo(field);
        return lt(column.getColumn(),column.getField());
    }
    public Sql lt(String field){
        return lt(toUnderline(field),field);
    }
    public Sql lt(String column, String filed){
        return operate("<",column,filed);
    }

    public Sql gt(Enum field){
        ColumnInfo column=EnumColumnUtil.getInfo(field);
        return gt(column.getColumn(),column.getField());
    }
    public Sql gt(String field){
        return gt(toUnderline(field),field);
    }
    public Sql gt(String column, String filed){
        return operate(">",column,filed);
    }
    public Sql ge(Enum field){
        ColumnInfo column=EnumColumnUtil.getInfo(field);
        return ge(column.getColumn(),column.getField());
    }
    public Sql ge(String field){
        return ge(toUnderline(field),field);
    }
    public Sql ge(String column, String filed){
        return operate(">=",column,filed);
    }
    public Sql le(Enum field){
        ColumnInfo column=EnumColumnUtil.getInfo(field);
        return le(column.getColumn(),column.getField());
    }
    public Sql le(String field){
        return le(toUnderline(field),field);
    }
    public Sql le(String column, String filed){
        return operate("<=",column,filed);
    }

    public Sql ne(Enum field){
        ColumnInfo column=EnumColumnUtil.getInfo(field);
        return ne(column.getColumn(),column.getField());
    }
    public Sql ne(String field){
        return ne(toUnderline(field),field);
    }
    public Sql ne(String column, String filed){
        return operate("<>",column,filed);
    }
    public Sql like(Enum field){
        ColumnInfo column=EnumColumnUtil.getInfo(field);
        return like(column.getColumn(),column.getField());
    }
    public Sql like(String field){
        return like(toUnderline(field),field);
    }
    public Sql like(String column, String filed){
        return operate("like",column,filed);
    }

    public <T, R> Sql nil(Getter<T, R> column){
        Pair<String, String> fieldAndColumn = getColumnAndField(column);
        String col=fieldAndColumn.getValue();
        return nil(col);
    }
    public Sql nil(Enum field){
        ColumnInfo column=EnumColumnUtil.getInfo(field);
        return nil(column.getColumn());
    }
    public Sql nil(String column){
        operate("IS NULL ",column,null);
        return this;
    }
    public Sql notNil(Enum field){
        ColumnInfo column=EnumColumnUtil.getInfo(field);
        return notNil(column.getColumn());
    }
    public Sql notNil(String column){
        operate("IS NOT NULL ",column,null);
        return this;
    }
    public <T, R> Sql notNil(Getter<T, R> column){
        Pair<String, String> fieldAndColumn = getColumnAndField(column);
        String col=fieldAndColumn.getValue();
        return notNil(col);
    }
    public Sql operate(String operator, String column, String filed) {
        operator = operator.toUpperCase();
        Matcher matcher = pattern.matcher(wherePart);
        String logic = "";
        if (matcher.find()) {
            wherePart = new StringBuffer();
            logic = matcher.group(1);
            matcher.appendReplacement(wherePart, "");
            matcher.appendTail(wherePart);
        }else if(wherePart.length()>0){
            logic="AND";
        }
        if(optional){
            wherePart.append("#[");
        }else{
            wherePart.append(" ");
        }

        wherePart.append(logic);
        if(StringUtil.isNotEmpty(logic)){
            wherePart.append(" ");
        }
        wherePart.append(column);
        wherePart.append(" ");
        if ("IN".equals(operator)) {
            wherePart.append("IN (:").append(filed).append(")");
        } else {
            if(filed!=null){
                wherePart.append(operator).append(" :").append(filed);
            }else{
                wherePart.append(operator);
            }
        }
        if(optional){
            wherePart.append("]");
        }
        return this;
    }
    public Sql operate(String operator, Getter column) {
        Pair<String, String> fieldAndColumn = getColumnAndField(column);
        return operate(operator,fieldAndColumn.getValue(),fieldAndColumn.getKey());
    }
    /**
     * 数据范围判断SQL，有对应字段时，自动拼接上对应查询条件
     * @param user 用户字段
     * @param branch 部门字段
     * @param role 角色字段
     * @return NQ
     */
    public Sql withScope(String user, String branch, String role){
        String scope = ScopeExecutor.getScope(Current.user(),user, branch, role);
        if(StringUtil.isEmpty(scope)){
            return this;
        }
        and();
        append(scope);
        return this;
    }
    public <T,R> Sql orderBy(Getter<T,R> ...columns){
        for(Getter<T,R> column:columns){
            Pair<String, String> fieldAndColumn = getColumnAndField(column);
            if(orderBy.length()>0){
                orderBy.append(",");
            }else{
                orderBy.append(" ");
            }
            orderBy.append(fieldAndColumn.getValue());
        }
        return this;
    }
    public <T,R> Sql orderByDesc(Getter<T,R> ...columns){
        for(Getter<T,R> column:columns){
            Pair<String, String> fieldAndColumn = getColumnAndField(column);
            if(orderBy.length()>0){
                orderBy.append(",");
            }else{
                orderBy.append(" ");
            }
            orderBy.append(fieldAndColumn.getValue());
            orderBy.append(" DESC");
        }
        return this;
    }
    public <T,R> Sql orderByDesc(String ...fields){
        for(String f:fields){
            if(orderBy.length()>0){
                orderBy.append(",");
            }else{
                orderBy.append(" ");
            }
            orderBy.append(toUnderline(f));
            orderBy.append(" DESC");
        }
        return this;
    }
    public <T,R> Sql orderByDesc(Enum ...fields){
        for(Enum f:fields){
            if(orderBy.length()>0){
                orderBy.append(",");
            }else{
                orderBy.append(" ");
            }
            ColumnInfo column=EnumColumnUtil.getInfo(f);
            orderBy.append(column.getColumn());
            orderBy.append(" DESC");
        }
        return this;
    }
    //添加排序描述，如：age asc,name desc
    public <T,R> Sql orderBy(String sortStr){
        this.orderBy.append(sortStr);
        return this;
    }
    @Override
    public String toString() {
        String select = "SELECT " + String.join(",", selects) + " FROM " + table;
        String sql= wherePart.length() > 0 ? select + " WHERE " + wherePart : select;
        if(orderBy.length()>0){
            sql+=" ORDER BY "+orderBy;
        }
        return sql;
    }
    public String toString(boolean useNameParam){
        return  useNameParam?toString():toString().replaceAll(":\\w+","?");
    }
    public String wherePart(){
        return wherePart.toString();
    }

    public Pair<String, String> getColumnAndField(Getter column) {
        ColumnInfo columnInfo = ColumnInfo.getColumnInfo(column);
        return new Pair<>(columnInfo.getField(), columnInfo.getColumn());
    }
    private static String toUnderline(String source) {
        if (source == null) {
            return null;
        }
        boolean isFirstChar=true;
        StringBuilder result = new StringBuilder();
        for (int i = 0; i < source.length(); i++) {
            char _char = source.charAt(i);
            if (_char >= 65 && _char <= 90) {
                if(!isFirstChar){
                    result.append('_');
                }
                result.append(Character.toLowerCase(_char));
            } else {
                result.append(_char);
            }
            if(isFirstChar){
                if(!Character.isSpaceChar(_char)){
                    isFirstChar=false;
                }
            }
        }
        return result.toString();
    }
}
